<?php

function EJInsertBill(&$billParams, $fid) {
    if (!EJConnectDB()) {
        W3LogWarning("No DB connection when insert bill");
        return false;
    }

    // First insert bill

    $columns = array ("PID", "Datetime", "Amount", "Currency", "Category", "PaymentMode", "Note");
    $size = sizeof($columns);

    $uselessParamCount = 3; # Match string, session, etc.
    $paramOffset = 1; # 0 is for the whole match string in reg
    if (sizeof($billParams) - $uselessParamCount != $size) {
        W3LogWarning("No enough fields for bill insert: require " .
                   strval($size) .
                   " but actual is " .
                   strval(sizeof($billParams) - $paramOffset));
        return false;
    }

    $aid = "aidAddBill";
    $billNote = EJDecodeURLString($billParams[W3GetAPIParamIndex($aid, "note") + $paramOffset]);
    $values = array($billParams[W3GetAPIParamIndex($aid, "owner") + $paramOffset],
                    W3MakeSingleQuotString($billParams[W3GetAPIParamIndex($aid, "datetime") + $paramOffset]),
                    $billParams[W3GetAPIParamIndex($aid, "amount") + $paramOffset],
                    $billParams[W3GetAPIParamIndex($aid, "currency") + $paramOffset],
                    $billParams[W3GetAPIParamIndex($aid, "category") + $paramOffset],
                    $billParams[W3GetAPIParamIndex($aid, "paymentmode") + $paramOffset],
                    W3MakeSingleQuotString($billNote));
    $sql = "insert into bill (" . implode(",", $columns) . ") values (" . implode("," , $values) . ")";

    if (!EJExecuteSQL($sql)) {
        W3LogWarning("Execute bill insert SQL failed");
        return false;
    }

    // Then map bill to finance event

    $events = EJDecodeURLString($billParams[W3GetAPIParamIndex($aid, "event") + $paramOffset]);
    $eventIDs = EJConvertEventStringToIDs($events, $fid);
    if (sizeof($eventIDs) == 0) {
        return true;
    }

    // [WARNING] Use max pid to find bill, it will failed for multi-thread
    $pid = $billParams[W3GetAPIParamIndex($aid, "owner") + $paramOffset];
    $maxIDSql = "select max(ID) from bill where bill.PID=" . $pid;

    EJReadTable($maxIDSql, function ($row) use ($eventIDs) {
        $billId = $row["max(ID)"];
        if (trim($billId) == "") {
            return;
        }

        foreach ($eventIDs as $eventId) {
            $insertSql = "insert into mapbillfinanceevent (Bill, Event) values (" .
                           $billId . "," . $eventId . ")";
            if (!EJExecuteSQL($insertSql)) {
                W3LogWarning("Execute map bill and finance event insert SQL failed");
            }
        }
    });

    return true;
}

function EJInsertDebt(&$debtParams, $fid) {
    if (!EJConnectDB()) {
        W3LogWarning("No DB connection when insert debt");
        return false;
    }

    $columns = array ("Start", "End", "Amount", "Balance", "Note", "FID");
    $size = sizeof($columns);

    $uselessParamCount = 1; # Match string, session, etc.
    $paramOffset = 1; # 0 is for the whole match string in reg
    if (sizeof($debtParams) - $uselessParamCount != $size) {
        W3LogWarning("No enough fields for debt insert: require " .
                   strval($size) .
                   " but actual is " .
                   strval(sizeof($debtParams) - $paramOffset));
        return false;
    }

    $aid = "aidAddDebt";
    $debtNote = EJDecodeURLString($debtParams[W3GetAPIParamIndex($aid, "note") + $paramOffset]);
    $balanceVal = $debtParams[W3GetAPIParamIndex($aid, "balance") + $paramOffset];
    if ($balanceVal == "") {
        $balanceVal = "0";
    }
    $values = array(W3MakeSingleQuotString($debtParams[W3GetAPIParamIndex($aid, "start") + $paramOffset]),
                    W3MakeSingleQuotString($debtParams[W3GetAPIParamIndex($aid, "end") + $paramOffset]),
                    $debtParams[W3GetAPIParamIndex($aid, "amount") + $paramOffset],
                    $balanceVal,
                    W3MakeSingleQuotString($debtNote),
                    $fid);
    $sql = "insert into debt (" . implode(",", $columns) . ") values (" . implode("," , $values) . ")";
    if (!EJExecuteSQL($sql)) {
        W3LogWarning("Execute debt insert SQL failed");
        return false;
    }

    return true;
}

function EJInsertFinanceEvent(&$eventParams, $fid) {
    if (!EJConnectDB()) {
        W3LogWarning("No DB connection when insert finance event");
        return false;
    }

    $columns = array ("Name", "Budget", "Note", "FID");
    $size = sizeof($columns);

    $uselessParamCount = 1; # Match string, session, etc.
    $paramOffset = 1; # 0 is for the whole match string in reg
    if (sizeof($eventParams) - $uselessParamCount != $size) {
        W3LogWarning("No enough fields for finance event insert: require " .
                   strval($size) .
                   " but actual is " .
                   strval(sizeof($eventParams) - $paramOffset));
        return false;
    }

    $aid = "aidAddFinanceEvent";
    $eventName = EJDecodeURLString($eventParams[W3GetAPIParamIndex($aid, "name") + $paramOffset]);
    $eventNote = EJDecodeURLString($eventParams[W3GetAPIParamIndex($aid, "note") + $paramOffset]);
    $values = array(W3MakeSingleQuotString($eventName),
                    $eventParams[W3GetAPIParamIndex($aid, "budget") + $paramOffset],
                    W3MakeSingleQuotString($eventNote),
                    $fid);
    $sql = "insert into financeevent (" . implode(",", $columns) . ") values (" . implode("," , $values) . ")";
    if (!EJExecuteSQL($sql)) {
        W3LogWarning("Execute finance event insert SQL failed");
        return false;
    }

    return true;
}

function EJInsertIncome(&$incomeParams) {
    if (!EJConnectDB()) {
        W3LogWarning("No DB connection when insert income");
        return false;
    }

    $columns = array ("PID", "Datetime", "Amount", "Currency", "Category", "Note");
    $size = sizeof($columns);

    $uselessParamCount = 2; # Match string, session, etc.
    $paramOffset = 1; # 0 is for the whole match string in reg
    if (sizeof($incomeParams) - $uselessParamCount != $size) {
        W3LogWarning("No enough fields for income insert: require " .
                   strval($size) .
                   " but actual is " .
                   strval(sizeof($incomeParams) - $paramOffset));
        return false;
    }

    $aid = "aidAddIncome";
    $incomeNote = EJDecodeURLString($incomeParams[W3GetAPIParamIndex($aid, "note") + $paramOffset]);
    $values = array($incomeParams[W3GetAPIParamIndex($aid, "owner") + $paramOffset],
                    W3MakeSingleQuotString($incomeParams[W3GetAPIParamIndex($aid, "datetime") + $paramOffset]),
                    $incomeParams[W3GetAPIParamIndex($aid, "amount") + $paramOffset],
                    $incomeParams[W3GetAPIParamIndex($aid, "currency") + $paramOffset],
                    $incomeParams[W3GetAPIParamIndex($aid, "category") + $paramOffset],
                    W3MakeSingleQuotString($incomeNote));
    $sql = "insert into income (" . implode(",", $columns) . ") values (" . implode("," , $values) . ")";
    if (!EJExecuteSQL($sql)) {
        W3LogWarning("Execute income insert SQL failed");
        return false;
    }

    return true;
}

function EJInsertExchangeRate($date, $currency) {
    if (!EJConnectDB()) {
        W3LogWarning("No DB connection when insert exchange rate");
        return false;
    }

    $sql = "insert into exchangerate (Datetime, Currency) values ('" . $date . "', " . $currency . ");";
    if (!EJExecuteSQL($sql)) {
        W3LogWarning("Execute exchange rate insert SQL failed");
        return false;
    }

    return true;
}

function EJGetExchangeRateImpl($date, $currency) {
    $sql = "select " .
         "exchangerate.Rate as rate" .
         " from " .
         "exchangerate" .
         " where " .
         "exchangerate.Datetime = '" . $date . "'" .
         " and " .
         "exchangerate.Currency = " . $currency;
    
    $rate = NULL;
    $isDateCurrencyRecorded = false;
    EJReadTable($sql, function ($row) use (&$rate) {
        if (sizeof($row) > 0) {
            $rate = $row["rate"];
            $isDateCurrencyRecorded = true;
        }
    });

    if ($rate == NULL) {
        W3LogWarning("Exchange rate not found for (" . $date . ", " . $currency . ")");
        if (!$isDateCurrencyRecorded) {
            # Insert date and currency, and the rate will be updated by other script automatically later
            EJInsertExchangeRate($date, $currency);
        }
    }

    return $rate;
}

function EJGetExchangeRateString($date, $currency) {
    $rate = "100.0";
    # 1 - CNY
    if ($currency != "1") {
        $rate = EJGetExchangeRateImpl($date, $currency);
        if ($rate == NULL) {
            $rate = "0";
        }
    }

    return $rate;
}

function EJSumCurrency($sql) {
    $finalAmount = 0.0;
    EJReadTable($sql, function ($row) use (&$finalAmount) {
        $amount = floatval($row["amount"]);
        $date = explode(" ", $row["datetime"])[0];
        $currencyID = $row["currency"];

        $rate = floatval(EJGetExchangeRateString($date, $currencyID));
        $amount *= $rate / 100;

        $finalAmount += $amount;
    });

    return $finalAmount;
}

function EJCalculatePercentageReport($fid, $table, $column, $year, $month, &$reports) {
    $nextYear = $year;
    $nextMonth = $month + 1;
    if ($nextMonth == 13) {
        $nextMonth = 1;
        $nextYear = $year + 1;
    }

    $sqlID = "select ID from " . $table . " order by ID asc";
    $sqlBill = "select bill.Amount as amount, bill.Datetime as datetime, bill.Currency as currency from bill where " .
             "bill.Datetime < " . W3MakeDateSingleQuotString($nextYear, $nextMonth, 1) .
             " and " .
             "bill.Datetime >= " . W3MakeDateSingleQuotString($year, $month, 1) .
             " and " .
             "bill.PID in (select person.ID from person where person.FID=" . $fid . ")";
    $sqlBillYear = "select bill.Amount as amount, bill.Datetime as datetime, bill.Currency as currency from bill where " .
             "bill.Datetime < " . W3MakeDateSingleQuotString($nextYear, $nextMonth, 1) .
             " and " .
             "bill.Datetime >= " . W3MakeDateSingleQuotString($year, 1, 1) .
                 " and " .
             "bill.PID in (select person.ID from person where person.FID=" . $fid . ")";
    $amount = array();
    $amountYear = array();
    EJReadTable($sqlID, function ($row) use ($column, $sqlBill, $sqlBillYear, &$amount, &$amountYear) {
        $amountBill = EJSumCurrency($sqlBill . " and bill." . $column . " = " . $row["ID"]);
        array_push($amount, $amountBill);

        $amountBillYear = EJSumCurrency($sqlBillYear . " and bill." . $column . " = " . $row["ID"]);
        array_push($amountYear, $amountBillYear);
    });
    $totalAmount = array_sum($amount);
    $percentageStr = "";
    foreach ($amount as $value) {
        if ($totalAmount <= 0) {
            $percentageStr .= "0_";
        } else {
            $percentageStr .= strval(intval(($value / $totalAmount) * 10000)) . "_";
        }
    }
    $percentageStr = trim($percentageStr, "_");
    $totalAmountYear = array_sum($amountYear);
    $percentageStrYear = "";
    foreach ($amountYear as $value) {
        if ($totalAmountYear <= 0) {
            $percentageStrYear .= "0_";
        } else {
            $percentageStrYear .= strval(intval(($value / $totalAmountYear) * 10000)) . "_";
        }
    }
    $percentageStrYear = trim($percentageStrYear, "_");
    $reports["percentage"] = $percentageStr;
    $reports["percentageyear"] = $percentageStrYear;
}

function EJCalculateDebtReport($fid, $year, $month, &$reports, $isLastMonth = true) {
    $preYear = $year;
    $preMonth = $month - 1;
    if ($preMonth == 0) {
        $preMonth = 12;
        $preYear = $year - 1;
    }

    $nextYear = $year;
    $nextMonth = $month + 1;
    if ($nextMonth == 13) {
        $nextMonth = 1;
        $nextYear = $year + 1;
    }

    # debt of month
    $sql = "select sum(debt.Amount) from debt where " .
         "debt.Start < " . W3MakeDateSingleQuotString($nextYear, $nextMonth, 1) .
         " and " .
         "debt.End >= " . W3MakeDateSingleQuotString($year, $month, 1) .
         " and " .
         "debt.FID = " . $fid;
    $totalMonthDebt = 0.0;
    EJReadTable($sql, function ($row) use (&$totalMonthDebt) {
        $totalMonthDebt = floatval($row["sum(debt.Amount)"]); # [ED] PENDING: Need to consider balance of debt
    });

    if ($isLastMonth) {
        $reports["debt"] = $totalMonthDebt;
        $reports["debtyear"] = 0.0;
    }
    $reports["debtyear"] += $totalMonthDebt;
    
    if ($preMonth != 12) {
        EJCalculateDebtReport($fid, $preYear, $preMonth, $reports, false);
    }
}
    
function EJCalculateFinanceReport($year, $month, &$reports, $fid) {
    $preYear = $year;
    $preMonth = $month - 1;
    if ($preMonth == 0) {
        $preMonth = 12;
        $preYear = $year - 1;
    }

    $nextYear = $year;
    $nextMonth = $month + 1;
    if ($nextMonth == 13) {
        $nextMonth = 1;
        $nextYear = $year + 1;
    }

    # income of month
    $sql = "select income.Amount as amount, income.Datetime as datetime, income.Currency as currency from income where " .
         "income.Datetime >= " . W3MakeDateSingleQuotString($preYear, $preMonth, 1) .
         " and " .
         "income.Datetime < " . W3MakeDateSingleQuotString($year, $month, 1) .
         " and " .
         "income.PID in (select person.ID from person where person.FID=" . $fid . ")";
    $totalMonthIncome = EJSumCurrency($sql);

    # income of year
    $sql = "select income.Amount as amount, income.Datetime as datetime, income.Currency as currency from income where " .
         "income.Datetime >= " . W3MakeDateSingleQuotString($year - 1, 12, 1) .
         " and " .
         "income.Datetime < " . W3MakeDateSingleQuotString($year, $month, 1) .
         " and " .
         "income.PID in (select person.ID from person where person.FID=" . $fid . ")";
    $totalYearIncome = EJSumCurrency($sql);

    # consume of month
    $sql = "select bill.Amount as amount, bill.Datetime as datetime, bill.Currency as currency from bill where " .
         "bill.Datetime < " . W3MakeDateSingleQuotString($nextYear, $nextMonth, 1) .
         " and " .
         "bill.Datetime >= " . W3MakeDateSingleQuotString($year, $month, 1) .
         " and " .
         "bill.PID in (select person.ID from person where person.FID=" . $fid . ")";
    $totalMonthConsume = EJSumCurrency($sql);

    # consume of year
    $sql = "select bill.Amount as amount, bill.Datetime as datetime, bill.Currency as currency from bill where " .
         "bill.Datetime < " . W3MakeDateSingleQuotString($nextYear, $nextMonth, 1) .
         " and " .
         "bill.Datetime >= " . W3MakeDateSingleQuotString($year, 1, 1) .
         " and " .
         "bill.PID in (select person.ID from person where person.FID=" . $fid . ")";
    $totalYearConsume = EJSumCurrency($sql);

    # debt
    EJCalculateDebtReport($fid, $year, $month, $reports);
    $totalMonthDebt = $reports["debt"];
    $totalYearDebt = $reports["debtyear"];
    
    # deposit
    # Separate to 3 everage
    $totalMonthDeposit = $totalMonthIncome / 3;
    $totalYearDeposit = $totalYearIncome / 3;
    
    # balance
    $totalMonthBalance = $totalMonthIncome - $totalMonthDeposit - $totalMonthDebt - $totalMonthConsume;
    $totalYearBalance = $totalYearIncome - $totalYearDeposit - $totalYearDebt - $totalYearConsume;

    $reports["income"] = $totalMonthIncome;
    $reports["incomeyear"] = $totalYearIncome;
    $reports["deposit"] = $totalMonthDeposit;
    $reports["deposityear"] = $totalYearDeposit;
    $reports["consume"] = $totalMonthConsume;
    $reports["consumeyear"] = $totalYearConsume;
    $reports["balance"] = $totalMonthBalance;
    $reports["balanceyear"] = $totalYearBalance;

    # category
    EJCalculatePercentageReport($fid, "billcategory", "Category", $year, $month, $reports);
    $reports["category"] = $reports["percentage"];
    $reports["categoryyear"] = $reports["percentageyear"];

    # paymentmode
    EJCalculatePercentageReport($fid, "paymentmode", "PaymentMode", $year, $month, $reports);
    $reports["paymentmode"] = $reports["percentage"];
    $reports["paymentmodeyear"] = $reports["percentageyear"];
}

 ?>
